﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.ServiceRequester;
using UncleBobWebService.Models;

namespace UncleBobWebService.Database
{
    class QuestionDBAdapter : IDBAdapter
    {
        /// <summary>
        /// Creates a new question.
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public override Message CreateData(Message msg, object data)
        {
            ApplicationQuestions questions = (ApplicationQuestions)data;
            msg.Result = true;

            try
            {
                using (SqlConnection connection = new SqlConnection(mConnectionString))
                using (SqlCommand command = connection.CreateCommand())
                {
                    foreach (ApplicationQuestion question in questions.Questions)
                    {
                        string Catagory = ApplicationQuestion.ExtendedQuestion;
                        if (question.GetType() == typeof(MultipleChoiceQuestion))
                        {
                            Catagory = ApplicationQuestion.MultipleChoiceQuestion;
                        }

                        command.CommandText = string.Format("INSERT INTO Questions (JobID, Catagory, Question) VALUES ('{0}', '{1}', '{2}') SELECT SCOPE_IDENTITY()"
                            , questions.JobID, Catagory, question.Question);

                        try
                        {
                            connection.Open();
                            question.QuestionID = int.Parse(command.ExecuteScalar().ToString());
                            connection.Close();                            
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }

                        if (question.GetType() == typeof(MultipleChoiceQuestion))
                        {
                            MultipleChoiceQuestion mQ = (MultipleChoiceQuestion)question;
                            foreach (Answer a in mQ.PossibleAnswers.Values)
                            {
                                command.CommandText = string.Format("INSERT INTO QuestionAnswers (QuestionID, Answer, IsCorrect) VALUES ('{0}', '{1}', '{2}')"
                                 , question.QuestionID, a.TheAnswer, a.IsCorrect);

                                try
                                {
                                    connection.Open();
                                    command.ExecuteNonQuery();
                                    connection.Close();                                                                        
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine(ex.Message);
                                }

                            }
                        }
                    }                    
                }
            }
            catch (Exception ex)
            {
                msg.Extra = ex.Message;
                msg.Result = false;
            }

            return msg;
            
        }

        /// <summary>
        /// Returns a single question
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public override Message SearchForSpecificData(Message msg, object data)
        {
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            DataTable dtMQ = new DataTable();

            int ID = int.Parse(msg.Data.ToString());
            msg.Result = false;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = string.Format("SELECT * FROM Questions WHERE QuestionID = '{0}'", ID);

                SqlAdapter = new SqlDataAdapter(command);

                try
                {
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    connection.Close();
                }
                finally
                {
                    connection.Close();
                }

                if (dt.Rows.Count >= 1)
                {
                    DataRow row = dt.Rows[0];
                    int qID = int.Parse(row["QuestionID"].ToString());
                    string question = row["Question"].ToString();

                    if (ApplicationQuestion.ExtendedQuestion == row["Catagory"].ToString())
                    {
                        ExtendedQuestion exQ = new ExtendedQuestion();
                        exQ.QuestionID = qID;
                        exQ.Question = question;

                        msg.Data = exQ;
                        msg.Result = true;
                    }
                    else
                    {
                        command.CommandText = string.Format("SELECT AnswerID, Answer, IsCorrect FROM dbo.QuestionAnswers WHERE QuestionID = {0}", qID);
                        SqlAdapter = new SqlDataAdapter(command);
                        dtMQ.Clear();
                        connection.Open();
                        SqlAdapter.Fill(dtMQ);
                        connection.Close();

                        MultipleChoiceQuestion mQ = new MultipleChoiceQuestion();
                        mQ.QuestionID = qID;
                        mQ.Question = question;

                        foreach (DataRow r in dtMQ.Rows)
                        {
                            Answer a = new Answer(int.Parse(r["AnswerID"].ToString()), r["Answer"].ToString(), bool.Parse(r["IsCorrect"].ToString()));
                            mQ.AddPossibleAnswer(a);
                        }

                        msg.Data = mQ;
                        msg.Result = true;
                    }                                                            
                }
            }
            return msg;
        }

        /// <summary>
        /// Returns a complete set of Application Questions
        /// </summary>
        /// <param name="msg">Takes a message containing a jobmodel in the data section. The title must be in the job model.</param>
        /// <returns></returns>
        public override Message SearchForAllData(Message msg)
        {
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            DataTable dtMQ = new DataTable();
            ApplicationQuestions appQuestions = new ApplicationQuestions();
            JobModel job = (JobModel)msg.Data;
            msg = new Message();
            msg.Result = false;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = string.Format("SELECT dbo.Questions.Question, dbo.Questions.Catagory, dbo.Questions.QuestionID FROM dbo.Jobs INNER JOIN dbo.Questions ON dbo.Jobs.JobID = dbo.Questions.JobID WHERE dbo.Jobs.JobTitle = '{0}'", job.Title);
                // command.CommandText = string.Format("SELECT dbo.QuestionAnswers.AnswerID, dbo.Questions.QuestionID, dbo.Jobs.JobTitle, dbo.Questions.Catagory, dbo.Questions.Question, dbo.QuestionAnswers.Answer, dbo.QuestionAnswers.IsCorrect FROM dbo.Jobs INNER JOIN dbo.Questions ON dbo.Jobs.JobID = dbo.Questions.JobID INNER JOIN dbo.QuestionAnswers ON dbo.Questions.QuestionID = dbo.QuestionAnswers.QuestionID WHERE dbo.Jobs.JobTitle = '{0}'", job.Title);
                SqlAdapter = new SqlDataAdapter(command);

                try
                {
                    connection.Open();
                    SqlAdapter.Fill(dt);
                    connection.Close();
                    foreach (DataRow row in dt.Rows)
                    {
                        int qID = int.Parse(row["QuestionID"].ToString());
                        string question = row["Question"].ToString();
                        if (ApplicationQuestion.ExtendedQuestion == row["Catagory"].ToString())
                        {
                            ExtendedQuestion exQ = new ExtendedQuestion();
                            exQ.QuestionID = qID;
                            exQ.Question = question;
                            appQuestions.AddQuestion(exQ);
                        }
                        else
                        {
                            command.CommandText = string.Format("SELECT AnswerID, Answer, IsCorrect FROM dbo.QuestionAnswers WHERE QuestionID = {0}", qID);
                            SqlAdapter = new SqlDataAdapter(command);
                            dtMQ.Clear();
                            connection.Open();
                            SqlAdapter.Fill(dtMQ);
                            connection.Close();

                            MultipleChoiceQuestion mQ = new MultipleChoiceQuestion();
                            mQ.QuestionID = qID;
                            mQ.Question = question;

                            foreach (DataRow r in dtMQ.Rows)
                            {

                                Answer a = new Answer(int.Parse(r["AnswerID"].ToString()), r["Answer"].ToString(), bool.Parse(r["IsCorrect"].ToString()));
                                mQ.AddPossibleAnswer(a);

                            }

                            appQuestions.AddQuestion(mQ);
                        }
                    }

                    appQuestions.JobID = job.ID;
                    msg.Result = true;
                    msg.Data = appQuestions;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            return msg;
        }

        /// <summary>
        /// Updates a complete set of Application Questions
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public override Message EditSpecificData(Message msg, object data)
        {
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            DataTable dtMQ = new DataTable();
            MultipleChoiceQuestion mQ = msg.Data as MultipleChoiceQuestion;
            ExtendedQuestion eQ = msg.Data as ExtendedQuestion;
          
            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {

                if (msg.Data.GetType() == typeof(MultipleChoiceQuestion))
                {                    
                    command.CommandText = string.Format("UPDATE Questions SET Catagory='{0}', Question='{1}' WHERE QuestionID = {2}",
                                                     ApplicationQuestion.MultipleChoiceQuestion, mQ.Question, mQ.QuestionID);
                }
                else
                {                    
                    command.CommandText = string.Format("UPDATE Questions SET Catagory='{0}', Question='{1}' WHERE QuestionID = {2}",
                                                     ApplicationQuestion.ExtendedQuestion, eQ.Question, eQ.QuestionID);
                }
                   
                SqlAdapter = new SqlDataAdapter(command);

                try
                {
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    connection.Close();
                    msg.Result = false;
                }
                finally
                {
                    connection.Close();
                }

                if (msg.Data.GetType() == typeof(MultipleChoiceQuestion))
                {
                    foreach (Answer a in mQ.PossibleAnswers.Values)
                    {
                        command.CommandText = string.Format("UPDATE QuestionAnswers SET Answer='{0}', IsCorrect='{1}' WHERE AnswerID = {2}",
                                                     a.TheAnswer, a.IsCorrect, a.ID);

                        SqlAdapter = new SqlDataAdapter(command);

                        try
                        {
                            connection.Open();
                            SqlAdapter.Fill(dt);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                            connection.Close();
                            msg.Result = false;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }                                       
                }                               
            }

            return msg;        
        }

        /// <summary>
        ///  Deletes a single question
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public override Message DeleteSpecificData(Message msg, object data)
        {
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            ApplicationQuestion q = (ApplicationQuestion)msg.Data;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                try
                {
                    command.CommandText = string.Format("DELETE FROM Questions WHERE QuestionID = '{0}'", q.QuestionID);
                    SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                    connection.Close();

                    command.CommandText = string.Format("DELETE FROM QuestionAnswers WHERE QuestionID = '{0}'", q.QuestionID);
                    SqlAdapter = new SqlDataAdapter(command);
                    connection.Open();
                    SqlAdapter.Fill(dt);
                    connection.Close();
                    msg.Result = true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                }
            }


            return msg;
        }

        /// <summary>
        /// Deletes all questions
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public override ServiceRequester.Message DeleteAllData(ServiceRequester.Message msg)
        {
                        
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            ApplicationQuestions aQs = (ApplicationQuestions)msg.Data;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                try
                {
                    foreach (ApplicationQuestion q in aQs.Questions)
                    {
                        command.CommandText = string.Format("DELETE FROM Questions WHERE QuestionID = '{0}'", q.QuestionID);
                        SqlAdapter = new SqlDataAdapter(command);
                        connection.Open();
                        SqlAdapter.Fill(dt);
                        connection.Close();

                        command.CommandText = string.Format("DELETE FROM QuestionAnswers WHERE QuestionID = '{0}'", q.QuestionID);
                        SqlAdapter = new SqlDataAdapter(command);
                        connection.Open();
                        SqlAdapter.Fill(dt);
                        connection.Close();
                        msg.Result = true;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                }            
            }

            return msg;
        }
    }
}
